
clear all
set more off


//change dir
global dir "../../"
global des "./1_AIS_data_summstats\output" 

cd "$dir" 

//here table number refers to paper v9
**************Table 1***************************************************************************
*T1: Ship: number of ports and number of distinct ports
use ./data_intermediate/ship_port_to_port_final.dta,clear

preserve 
bys ship_id_new: egen nport=count(D_acid) //depart-arrival-depart-arrival-depart-arrival
keep ship_id nport
duplicates drop
save tmp0,replace 
restore 

preserve 
keep ship_id_new D_acid
save tmp,replace
restore 
keep ship_id_new A_acid
rename A_a D_acid
append using  tmp
duplicates drop
bysort ship_id: egen nport_dist=count(D_acid)
keep ship_id nport 
duplicates drop
merge 1:1 ship using tmp0, nogen 
save tmp0,replace 

estpost sum nport nport_dist , detail 
cap erase "$des\T1-ship-stats.xls"
esttab using "$des\T1-ship-stats.xls",replace cells("count p50 mean sd min max") noobs
	
	
*T1: Port number of incoming and outgoing ships 
use ./data_intermediate/ship_port_to_port_final.dta,clear

bys A_acid: egen n_incoming_ships=count(ship_id_new) 
bys D_acid: egen n_outgoing_ships=count(ship_id_new) 
preserve 
keep A_acid n_incoming_ships
duplicates drop 
save tmp0,replace
restore 
keep D_acid n_outgoing_ships
rename D_a A_acid
duplicates drop
merge 1:1 A_acid  using tmp0, nogen 

estpost sum n_incoming_ships n_outgoing_ships , detail 
esttab using "$des\T1-ship-stats.xls",append cells("count  p50 mean sd min max") noobs
		

*T1: Port paris: ships passing through 
use ./data_intermediate/ship_port_to_port_final.dta,clear

bys A_acid D_acid: egen n_ships=count(ship_id_new) 
bys A_acid D_acid: egen n_dwt=sum(dwt_usage_55) 
keep A_acid D_acid n_ships n_dwt
duplicates drop
replace n_dwt=n_dwt/1000000
*drop if n_dwt==0  //drop port pairs that have zero traffics (no need, obs=0 when set cutoff at 55)
estpost sum n_ships n_dwt, detail 
esttab using "$des\T1-ship-stats.xls",append cells("count p50 mean sd min max") noobs



**************Table 2 Port Networks***************************************************************************
use ./data_intermediate/ship_port_to_port_final.dta,clear
keep D_acid A_acid
duplicates drop 

bys D: egen oud=count(A)
bys A: egen ind=count(D)

preserve 
keep A_acid ind
duplicates drop 
save tmp0,replace
restore 
keep D_acid oud
duplicates drop 
rename D_acid A_acid
merge 1:1 A_acid using tmp0

estpost sum  ind oud, detail 
esttab using "$des\T2-port networks.xls",replace cells("count p50 p90 p95 p99 mean sd min max") noobs



**************Table 9 Ships, Trips and Port***************************************************************************

use ./inga/data/ship_port_to_port_final.dta,clear

preserve 
collapse (mean) ballast_55,by(ship_id)
estpost sum ballast
esttab using "$des\T9-shiptripports.xls",replace cells("count mean sd min max") noobs
restore 

preserve
replace draughtm=A_new_draught if draughtm==.
replace draughtm=D_new_draught if draughtm==.
replace draughtm=draught_s if draughtm>=draught_s & draughtm!=.
gen sh=draughtm/draught_s
estpost sum sh  if dwt_usage_55!=.&dwt_usage_55!=0
esttab using "$des\T9-shiptripports.xls",append cells("count mean sd min max") noobs
estpost sum dwt_usage_55 if dwt_usage_55!=.&dwt_usage_55!=0
esttab using "$des\T9-shiptripports.xls",append cells("count mean sd min max") noobs
restore 


bys A_acid: egen n_incoming_ships=sum(dwt_usage_55) 
bys D_acid: egen n_outgoing_ships=sum(dwt_usage_55) 
preserve 
keep A_acid n_incoming_ships
duplicates drop 
save tmp0,replace
restore 
keep D_acid n_outgoing_ships
rename D_a A_acid
duplicates drop
merge 1:1 A_acid  using tmp0, nogen 
rename *ships *dtw
replace n_outgoing_dtw=n_outgoing_dtw/1000000
replace n_incoming_dtw=n_incoming_dtw/1000000
estpost sum n_incoming n_outgoing
esttab using "$des\T9-shiptripports.xls",append cells("count  mean sd min max") noobs